package com.information.manage.basic.common.utils.exportAndImport.custom;

import cn.afterturn.easypoi.exception.excel.ExcelExportException;
import jxl.format.Alignment;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import jxl.write.Label;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Sheet;

import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.Font;
import java.awt.font.TextAttribute;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.Boolean;
import java.text.AttributedCharacterIterator;
import java.text.AttributedString;
import java.util.Iterator;

import static java.awt.image.BufferedImage.TYPE_INT_ARGB;

/**
 * Created with IntelliJ IDEA.
 * User: Initial Heart
 * Date: 2022-08-11.
 * Description:  excel单元格操作
 * Version: V1.0
 */
@Slf4j
public class TablelOperate {

    /**
     * 填充值
     * @param sheet
     * @param row 行
     * @param col 列
     * @param value 值
     * @param cellFormat 样式
     */
    protected static void fullValue(WritableSheet sheet, int row, int col, String value, CellFormat cellFormat){
        Label label = new Label(col, row, value, cellFormat);
        try {
            sheet.addCell(label);
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置行高列宽填充值
     * @param sheet
     * @param row 行
     * @param col 列
     * @param rowHeight 行高
     * @param colWidth 列宽
     * @param value 值
     * @param cellFormat 样式
     */
    protected static void fullValue(WritableSheet sheet, int row, int col, Integer rowHeight, Integer colWidth, String value, CellFormat cellFormat){
        try {
            if (colWidth != null && colWidth > 0) sheet.setColumnView(col, colWidth);
            if (rowHeight != null && rowHeight > 0) sheet.setRowView(row, rowHeight, false);
            fullValue(sheet, row, col, value, cellFormat);
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 填充图片  只支持png格式
     * @param sheet
     * @param row 行
     * @param col 列
     * @param width 宽
     * @param height 高
     * @param path 图片路径
     */
    protected static void fullImage(WritableSheet sheet, int row, int col, int width, int height, String path){
        File file = new File(path);
        WritableImage image = new WritableImage(col, row, width, height, file);
        sheet.addImage(image);
    }

    /**
     * 合并单元格并填充值
     * @param sheet
     * @param row1 起始行
     * @param row2 终止行
     * @param col1 起始列
     * @param col2 终止列
     * @param value 值
     * @param cellFormat 样式
     */
    protected static void mergeCellsAndFullValue(WritableSheet sheet, int row1, int row2, int col1, int col2, String value, CellFormat cellFormat){
        try {
            sheet.mergeCells(col1, row1, col2, row2);
            fullValue(sheet, row1, col1, value, cellFormat);
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 合并单元格设置行高列宽并填充值
     * @param sheet
     * @param row1 起始行
     * @param row2 终止行
     * @param col1 起始列
     * @param col2 终止列
     * @param rowHeight 行高
     * @param colWidth 列宽
     * @param value 值
     * @param cellFormat 样式
     */
    protected static void mergeCellsAndFullValue(WritableSheet sheet, int row1, int row2, int col1, int col2, Integer rowHeight, Integer colWidth, String value, CellFormat cellFormat){
        try {
            if (colWidth != null && colWidth > 0) sheet.setColumnView(col1, colWidth);
            if (rowHeight != null && rowHeight > 0) sheet.setRowView(row1, rowHeight, false);
            mergeCellsAndFullValue(sheet, row1, row2, col1, col2, value, cellFormat);
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 条件填充值及样式
     * @param sheet
     * @param condition 满足的条件
     * @param row 行
     * @param col 列
     * @param value 值
     * @param cellFormat1 满足条件的样式
     * @param cellFormat2 不满足条件的样式
     */
    protected static void conditionFullValueAndStyle(WritableSheet sheet, boolean condition, int row, int col, String value, CellFormat cellFormat1, CellFormat cellFormat2) {
        Label label;
        if (condition) {
            label = new Label(col, row, value, cellFormat1);
        } else {
            label = new Label(col, row, value, cellFormat2);
        }
        try {
            sheet.addCell(label);
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 工具类 水印、斜划线
     */
    public static class utils {

        /**
         * 添加水印
         * @param os 保存的输出流
         * @param resourcePath 读取的资源文件后的地址
         * @param text 水印文字
         * @throws IOException
         */
        public static void addWaterMark(OutputStream os, String resourcePath, String text) throws IOException {
            InputStream inputStream = TablelOperate.utils.class.getClassLoader().getResourceAsStream(resourcePath);
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
            Iterator<Sheet> sheetIterator = hssfWorkbook.sheetIterator();
            while (sheetIterator.hasNext()){
                HSSFSheet sheet = (HSSFSheet) sheetIterator.next();
                BufferedImage bufferedImage = createWaterMarkImage(text);
                putWaterMarkToExcel(hssfWorkbook, sheet, bufferedImage,4, 4, 4, 4, 2, 2, 3, 4);
            }
            hssfWorkbook.write(os);
        }

        /**
         * 根据文字生成水印图片
         * @param text 水印文字
         * @return
         */
        private static BufferedImage createWaterMarkImage(String text) {
            final int WIDTH = 360;
            final int HEIGHT = 150;
            // 水印横向位置
            int positionWidth = 80;
            int positionHeight = 30;
            //水印文字 字体类型、风格、大小 microsoft-yahei、微软雅黑、宋体
            Font font = new Font("微软雅黑", Font.PLAIN, 20);
            // 水印文字颜色
            Color color = new Color(0, 0, 0, 80);
            // 获取bufferedImage对象创建空白图片
            BufferedImage image = new BufferedImage(WIDTH, HEIGHT, TYPE_INT_ARGB);
            // 获取Graphics2d对象创建图片画笔
            Graphics2D g = image.createGraphics();
            // 设置背景透明度
            image = g.getDeviceConfiguration().createCompatibleImage(WIDTH, HEIGHT, Transparency.TRANSLUCENT);
            g.dispose();
            g = image.createGraphics();
            // 设置对线段的锯齿状边缘处理
            // g.setRenderingHint(RenderingHints.KEY_INTERPOLATION, RenderingHints.VALUE_INTERPOLATION_BILINEAR);
            g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
            // 文字处理
            AttributedString ats = new AttributedString(text);
            ats.addAttribute(TextAttribute.FONT, font, 0, text.length());
            AttributedCharacterIterator iter = ats.getIterator();
            // 水印旋转
            g.rotate(Math.toRadians(-15), (double) image.getWidth() / 2, (double) image.getHeight() / 2);
            // 设置水印文字颜色
            g.setColor(color);
            // 设置水印字体加粗
            g.setStroke(new BasicStroke(1));
            // 设置水印文字Font
            g.setFont(font);
            // 设置水印文字透明度
            g.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
            // 水印位置
            g.drawString(iter, positionHeight, positionWidth);
            // 释放资源
            g.dispose();
            return image;
        }

        /**
         * 为Excel打上水印工具函数 请自行确保参数值，以保证水印图片之间不会覆盖。
         *
         * @param wb              Excel Workbook
         * @param sheet           需要打水印的Excel
         * @param image           水印图片
         * @param startXCol       水印起始列
         * @param startYRow       水印起始行
         * @param betweenXCol     水印横向之间间隔多少列
         * @param betweenYRow     水印纵向之间间隔多少行
         * @param XCount          横向共有水印多少个
         * @param YCount          纵向共有水印多少个
         * @param waterMarkWidth  水印图片宽度为多少列
         * @param waterMarkHeight 水印图片高度为多少行
         * @throws IOException
         */
        private static void putWaterMarkToExcel(org.apache.poi.ss.usermodel.Workbook wb, Sheet sheet, BufferedImage image, int startXCol,
                                                int startYRow, int betweenXCol, int betweenYRow, int XCount, int YCount, int waterMarkWidth,
                                                int waterMarkHeight) throws IOException {
            // 加载图片
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            if (null == image) {
                throw new ExcelExportException("向Excel上面打印水印，读取水印图片失败！");
            }
            ImageIO.write(image, "png", byteArrayOut);
            // 开始打水印
            Drawing drawing = sheet.createDrawingPatriarch();
            // 按照共需打印多少行水印进行循环
            for (int yCount = 0; yCount < YCount; yCount++) {
                // 按照每行需要打印多少个水印进行循环
                for (int xCount = 0; xCount < XCount; xCount++) {
                    // 创建水印图片位置
                    int xIndexInteger = startXCol + (xCount * waterMarkWidth) + (xCount * betweenXCol);
                    int yIndexInteger = startYRow + (yCount * waterMarkHeight) + (yCount * betweenYRow);
                    /*
                     * 参数定义： 第一个参数是（x轴的开始节点）； 第二个参数是（是y轴的开始节点）； 第三个参数是（是x轴的结束节点）；
                     * 第四个参数是（是y轴的结束节点）； 第五个参数是（是从Excel的第几列开始插入图片，从0开始计数）；
                     * 第六个参数是（是从excel的第几行开始插入图片，从0开始计数）； 第七个参数是（图片宽度，共多少列）；
                     * 第8个参数是（图片高度，共多少行）；
                     */
                    ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, xIndexInteger, yIndexInteger, xIndexInteger + waterMarkWidth, yIndexInteger + waterMarkHeight);
                    Picture pic = drawing.createPicture(anchor,
                            wb.addPicture(byteArrayOut.toByteArray(), org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_PNG));
                    pic.resize();
                }
            }
        }

        /**
         * 单元格斜划线
         * @param os 内容
         * @param path 文件存储路径
         * @param i 行
         * @param j 列
         * @param width 单元格宽度
         * @param height 单元格高度
         */
        public static void drawLine(OutputStream os, String path, int i, int j, int width, int height) throws IOException {
            final int PERCENT_WIDTH = 50;
            final int PERCENT_HEIGHT = 20;
            final float PXTOPT = 0.75f;
            int cellWidth = (int) (PERCENT_WIDTH * PXTOPT * width);
            short cellHeight = (short) (PERCENT_HEIGHT * PXTOPT * height);
            InputStream inputStream =  TablelOperate.utils.class.getClassLoader().getResourceAsStream("tmp/分段tmp.xls");
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
            Iterator<Sheet> sheetIterator = hssfWorkbook.sheetIterator();
            while (sheetIterator.hasNext()){
                HSSFSheet sheet = (HSSFSheet) sheetIterator.next();
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255, (short) j, i, (short) (j), i);
                HSSFShapeGroup group = patriarch.createGroup(a);
                float verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet);
                EscherGraphics g = new EscherGraphics(group, sheet.getWorkbook(), Color.black, verticalPointsPerPixel);
                EscherGraphics2d g2d = new EscherGraphics2d(g);
                int[] xys = {width, height};
                for (int l = 0; l < xys.length; l += 2) {
                    int x = (int) ((PERCENT_WIDTH * 0.75 * xys[l] / cellWidth) * 1023);
                    int y = (int) ((PERCENT_HEIGHT * 0.75 * xys[l + 1] / cellHeight) * 255);
                    //g2d.drawLine(0, 0, x, y); 左上右下
                    g2d.drawLine(0, y, x, 0);
                }
            }
            hssfWorkbook.write(os);
        }

    }

    /**
     * 样式类
     * 添加样式时按照命名规范 输入样式名称即可自动生成样式
     */
    public static class style {

        // 命名格式： 字体颜色_字体大小_是否加粗_水平位置_垂直位置_背景颜色_是否自动换行
//        public static WritableCellFormat BLACK_10_TRUE_CENTRE_CENTRE_NULL_TRUE;
//        public static WritableCellFormat BLACK_10_FALSE_CENTRE_CENTRE_NULL_TRUE;
//        public static WritableCellFormat BLACK_10_TRUE_LEFT_CENTRE_NULL_TRUE;
//        public static WritableCellFormat BLACK_10_FALSE_LEFT_CENTRE_NULL_TRUE;
//        public static WritableCellFormat BLACK_10_TRUE_LEFT_CENTRE_RED_TRUE;
//        public static WritableCellFormat BLACK_10_FALSE_CENTRE_CENTRE_YELLOW_TRUE;
//        public static WritableCellFormat BLACK_10_TRUE_CENTRE_CENTRE_YELLOW_TRUE;
//        public static WritableCellFormat BLACK_14_TRUE_CENTRE_CENTRE_NULL_TRUE;
//        public static WritableCellFormat BLACK_14_TRUE_LEFT_CENTRE_NULL_TRUE;
//        public static WritableCellFormat RED_10_TRUE_LEFT_CENTRE_YELLOW_TRUE;
//        public static WritableCellFormat RED_10_FALSE_CENTRE_CENTRE_NULL_TRUE;
//        public static WritableCellFormat RED_10_FALSE_CENTRE_CENTRE_YELLOW_TRUE;

//        static {
//            try {
//                Class<style> styleClass = style.class;
//                style style = styleClass.newInstance();
//                Field[] declaredFields = styleClass.getDeclaredFields();
//                for (Field declaredField : declaredFields) {
//                    // 仅生成WritableCellFormat类型的
//                    if (declaredField.getType().getName().equals("jxl.write.WritableCellFormat")){
//                        String name = declaredField.getName();
//                        // 给规范命名的样式赋值
//                        styleClass.getDeclaredField(name).set(style, generateCellFormat(name));
//                    }
//                }
//            } catch (IllegalAccessException e) {
//                e.printStackTrace();
//            } catch (NoSuchFieldException e) {
//                e.printStackTrace();
//            } catch (InstantiationException e) {
//                e.printStackTrace();
//            }
//        }

        /**
         * 生成表格样式
         * @param str 样式命名
         * 规则： 命名格式
         *    字体颜色_字体大小_是否加粗_水平位置_垂直位置_背景颜色_是否自动换行
         *    例：BLACK_14_TRUE_LEFT_CENTRE_NULL_TRUE
         * 字体颜色 RED、BLACK。。。
         * 字体大小  10、12。。。
         * 是否加粗 true / false
         * 水平方向位置 GENERAL、LEFT、CENTRE、RIGHT、FILL、JUSTIFY
         * 垂直方向位置 TOP、CENTRE、BOTTOM、JUSTIFY （默认CENTRE）
         * 背景颜色 RED、BLACK。。。
         * 是否自动换行 true / false
         * @return
         */
        protected static WritableCellFormat generateCellFormat(String str){
            String[] sp = str.split("_");
            try {
                Colour fontColour = getColour(sp[0]);
                Integer fontSize = Integer.valueOf(sp[1]);
                Boolean isBold = Boolean.valueOf(sp[2]);
                Alignment alignment = getAlignment(sp[3]);
                VerticalAlignment verticalAlignment = getVerticalAlignment(sp[4]);
                Colour backGround = getColour(sp[5]);
                Boolean isAutoLineFeed = Boolean.valueOf(sp[6]);
                return cellStyle(fontColour, WritableFont.ARIAL, fontSize, isBold, alignment, verticalAlignment, backGround, isAutoLineFeed);
            }catch (Exception e){
                log.info("样式命名不规范，生成表格样式{}失败", str);
            }
            return null;
        }

        /**
         * 获取Colour对象
         * @param str 字符串 数据为black、red等  大小写均可
         * @return
         */
        private static Colour getColour(String str){
            for (Colour colour : Colour.getAllColours()) {
                if (colour.getDescription().equalsIgnoreCase(str)){
                    return colour;
                }
            }
            return null;
        }

        /**
         * 获取Alignment对象
         * @param str 字符串 数据为CENTRE、left等 大小写均可
         * @return
         */
        private static Alignment getAlignment(String str){
            Alignment alignment;
            if (Alignment.CENTRE.getDescription().equalsIgnoreCase(str)){
                alignment = Alignment.CENTRE;
            }else if (Alignment.LEFT.getDescription().equalsIgnoreCase(str)){
                alignment = Alignment.LEFT;
            }else if (Alignment.RIGHT.getDescription().equalsIgnoreCase(str)){
                alignment = Alignment.RIGHT;
            }else if (Alignment.GENERAL.getDescription().equalsIgnoreCase(str)){
                alignment = Alignment.GENERAL;
            }else if (Alignment.FILL.getDescription().equalsIgnoreCase(str)){
                alignment = Alignment.FILL;
            }else if (Alignment.JUSTIFY.getDescription().equalsIgnoreCase(str)){
                alignment = Alignment.JUSTIFY;
            }else {
                alignment = null;
            }
            return alignment;
        }

        /**
         * 获取VerticalAlignment信息
         * @param str 字符串 数据为CENTRE、top等 大小写均可
         * @return
         */
        private static VerticalAlignment getVerticalAlignment(String str){
            VerticalAlignment verticalAlignment;
            if (VerticalAlignment.TOP.getDescription().equalsIgnoreCase(str)){
                verticalAlignment = VerticalAlignment.TOP;
            }else if (VerticalAlignment.CENTRE.getDescription().equalsIgnoreCase(str)){
                verticalAlignment = VerticalAlignment.CENTRE;
            }else if (VerticalAlignment.BOTTOM.getDescription().equalsIgnoreCase(str)){
                verticalAlignment = VerticalAlignment.BOTTOM;
            }else if (VerticalAlignment.JUSTIFY.getDescription().equalsIgnoreCase(str)){
                verticalAlignment = VerticalAlignment.JUSTIFY;
            }else {
                verticalAlignment = null;
            }
            return verticalAlignment;
        }

        /**
         * 单元格样式
         * @param fontName WritableFont.FontName 字体名称
         * @param fontColour 字体颜色 RED、BLACK。。。。
         * @param fontSize 字体大小
         * @param isBold 是否加粗 true加粗 / false不加粗
         * @param horizontal 水平方向位置 Alignment. + GENERAL、LEFT、CENTRE、RIGHT、FILL、JUSTIFY
         * @param verticalAlignment 垂直方向位置 VerticalAlignment. + TOP、CENTRE、BOTTOM、JUSTIFY
         * @param backGround 背景颜色
         * @param isAutoLineFeed 是否自动换行
         */
        protected static WritableCellFormat cellStyle(jxl.format.Colour fontColour, WritableFont.FontName fontName, int fontSize, boolean isBold
                , jxl.format.Alignment horizontal, jxl.format.VerticalAlignment verticalAlignment, jxl.format.Colour backGround, boolean isAutoLineFeed){
            try {
                WritableFont color;
                if (isBold){
                    color = new WritableFont(fontName,fontSize,WritableFont.BOLD);// 选择字体
                }else {
                    color = new WritableFont(fontName,fontSize,WritableFont.NO_BOLD);// 选择字体
                }
                if (fontColour != null) color.setColour(fontColour);// 设置字体颜色
                WritableCellFormat colorFormat = new WritableCellFormat(color);
                colorFormat.setAlignment(horizontal);// 单元格中的内容水平方向
                colorFormat.setVerticalAlignment(verticalAlignment);// 单元格的内容垂直方向
                if (backGround != null) colorFormat.setBackground(backGround);
                if (isAutoLineFeed){
                    colorFormat.setWrap(true);//自动换行
                }else {
                    colorFormat.setWrap(false);
                }
                return colorFormat;
            } catch (WriteException e) {
                e.printStackTrace();
            }
            return null;
        }

        /**
         * 通用（垂直居中、自动换行）
         * @param fontColour 字体颜色
         * @param fontSize 字体大小
         * @param isBold 是否加粗
         * @param horizontal 水平方向位置
         * @param backGround backGround
         * @return
         */
        protected static WritableCellFormat cellStyle(jxl.format.Colour fontColour, int fontSize, boolean isBold, jxl.format.Alignment horizontal, jxl.format.Colour backGround){
            return cellStyle(fontColour, WritableFont.ARIAL, fontSize, isBold, horizontal, VerticalAlignment.CENTRE, backGround, true);
        }

    }

}
